/**
 * @Copyright: 东方龙水业科技有限公司，石家庄
 * @Author: Airwolf, 2008-02-16
 * @Version: 080216.001
 * 对departure,package进行的联合查询，返回某公司两个日期间的总定团人数
 */
package com.ibsol.database
{
	import com.ibsol.model.TotalAvailable;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
    
  	public class TotalAvailableDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * 获取缺省连接
		 */					
		public function TotalAvailableDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		
		/**
		 * Load price according to package id
		 * @param:  companyid					公司ID
		 * 			startdate:string			起日期
		 * 			enddate:string				止日期
		 * 			resultHandler:Function	result function adapter, 返回某公司起止日期间的记录
		 * 			faultHandler:Function	fault funcition adapter
		 */		
		public function loadDepartureTotalAvailable(companyid:int, startdate:String, enddate:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = " Select a.packageid, b.name as packagename, sum(slot) as total_slot, " + 
						  " sum(slot - available) as total_available from ( " +
						  " select *, (cast(departureYear as text)||'-'" + 
						  "||case when departureMonth<10 then '0' else '' end||cast(departureMonth as text)||'-'" + 
						  "||case when departureDate<10 then '0' else '' end||cast(departureDate as text)) as date from [departure] " +
						  " where date >= " + "'" + startdate + "'" +
						  " and date <= " + "'" + enddate + "'" +
						  " ) as a  , [package] as b " +
						  " where a.packageid = b.id " + 
						  //" and b.companyid = " + companyid +
						  " group by a.packageid ";
				  
			trace(sqlStr.text);				  
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		    			
		}
		/**
		 * Converts array object to Photo array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Photo after conversion from Array
		 */		
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}		
		/**
		 * Converts database object to TotalAvailable class
		 * @param:	o:Object				database return object
		 * @return:	TotalAvailable		TotalAvailable object after conversion
		 */		
		private function typeObject(o:Object):TotalAvailable
		{
			var p:TotalAvailable = new TotalAvailable();
			p.packageid = o.packageid;
			p.packagename = o.packagename;
			p.total_slot = o.total_slot;
			p.total_available = o.total_available;
			return p;
		}		
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}						
	
	}
}